package Negocio;

import java.sql.*;
import java.util.Map;
import java.util.Map.Entry;
import java.util.logging.*;

/**
 *
 * @author sabidosan, ...
 */
public class AdminDB {

    //@Resource(name = "jdbc/dbBYS")
    //DataSource ds;

    public AdminDB(){
    }
    
    public Connection getConnection() throws SQLException{
        
        String connURL= "jdbc:mysql://localhost:3306/bysDB";
        Connection conn = DriverManager.getConnection(connURL,"bys-dba","password");
        
        return conn;
        
        //return ds.getConnection();
    }

    public Integer getID(String tabla, String condicion){
    
       int id = 0;
       try
       {
           Connection conn = this.getConnection();
           Statement stmt = conn.createStatement();

           ResultSet rset = stmt.executeQuery("SELECT ID FROM `bysDB`.`"+tabla+"` WHERE "+condicion);
           
           if (rset.next()){ id = rset.getInt("ID"); }
           
           if(rset != null) rset.close();
           if(stmt != null) stmt.close();
           if(conn != null) conn.close();

        } catch (Exception e) {
            Logger.getLogger("Recuperar ID").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
        }
       finally {
           return id;
        }
    }
    
    public void borrarFila(String tabla, String condicion){    
        try
       {    
           Connection conn = this.getConnection();
           Statement stmt = conn.createStatement();

           stmt.executeUpdate("DELETE FROM `bysDB`.`"+tabla+"` WHERE "+condicion);

           if (stmt != null) stmt.close();
           if (conn != null) conn.close();

        } catch (Exception e) {
            Logger.getLogger("BorrarFila").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
        }
    }

    public boolean guardar(ObjetoDB o) {
       try
       {
           Connection conn = this.getConnection();
           Statement stmt = conn.createStatement();
           
           String sentencia = "INSERT INTO `bysDB`.`"+o.getTabla()+"` (";
           String values = ") VALUES (";
           
           boolean first = true;
           for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {               
               if(first){
                   sentencia += "`"+entry.getKey()+"`";
                   values += "'"+entry.getValue()+"'";
                   first = false;
               }
               else if (o.getCampos().keySet().iterator().hasNext()){
                sentencia += ",`"+entry.getKey()+"`";
                values += ",'"+entry.getValue()+"'";
               }
            }           
           sentencia += values +");";

           stmt.executeUpdate(sentencia);

           if (stmt != null) stmt.close();
           if (conn != null)conn.close();

           return true;

        } catch (Exception e) {
            Logger.getLogger("Guardado en "+o.getTabla()).log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        }
    }
    
    public boolean actualizar(ObjetoDB o) {
       try
       {
           Connection conn = this.getConnection();
           Statement stmt = conn.createStatement();
           
           String sentencia = "UPDATE `bysDB`.`"+o.getTabla()+"` SET ";
           String where = " WHERE ";
           
           boolean first = true;
           for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {               
               if(first){
                   sentencia += entry.getKey()+"='"+entry.getValue()+"'";
                   where += "ID="+o.getId();
                   first = false;
               }
               else if (o.getCampos().keySet().iterator().hasNext()){
                sentencia += ","+entry.getKey()+"='"+entry.getValue()+"'";
               }
            }           
           sentencia += where;
           stmt.executeUpdate(sentencia);

           if (stmt != null) stmt.close();
           if (conn != null)conn.close();

           return true;

        } catch (Exception e) {
            Logger.getLogger("Actualizado "+o.getTabla()).log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        }
    }
    
    public boolean borrar(ObjetoDB o) {
       try
       {
           Connection conn = this.getConnection();
           Statement stmt = conn.createStatement();
           
           String sentencia = "DELETE FROM `bysDB`.`"+o.getTabla()+"` WHERE ";
           
           boolean first = true;
           for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {               
               if(first){
                   sentencia += entry.getKey()+"='"+entry.getValue()+"'";
                   first = false;
               }
               else if (o.getCampos().keySet().iterator().hasNext()){
                sentencia += " AND "+entry.getKey()+"='"+entry.getValue()+"'";
               }
            }
           stmt.executeUpdate(sentencia);

           if (stmt != null) stmt.close();
           if (conn != null)conn.close();

           return true;

        } catch (Exception e) {
            Logger.getLogger("Borrado "+o.getTabla()).log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        }
    }
    
    public boolean autentificar(String username, String password){
       boolean login = false;
       try
       {
           Connection conn = this.getConnection();
           Statement stmt = conn.createStatement();
           
           ResultSet rset = stmt.executeQuery("SELECT username, password FROM `bysDB`.`Usuario` WHERE username='"+username+"' AND password='"+password+"';");
          
           if (rset.next()) login = true;
           if (rset!=null) rset.close();
           if (stmt != null) stmt.close();
           if (conn != null)conn.close();
           
           return login;

        } catch (Exception e) {
            Logger.getLogger("Autentificar").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        }
    }
    
    public String toSQLDate(String fecha){//20-10-2011
        String dia = fecha.substring(0, 2);
        String mes = fecha.substring(3, 5);
        String ano = fecha.substring(6);
        
        return ano+"-"+mes+"-"+dia;//2011-12-10
    }
    
    public String toDMY(String fecha){//2011-12-10
        String ano = fecha.substring(0, 4);
        String mes = fecha.substring(5, 7);
        String dia = fecha.substring(8);
        
        String mmm = null;
        int m = Integer.parseInt(mes);
        switch (m) {
            case 1: mmm = "ENE";
            case 2: mmm = "FEB";
            case 3: mmm = "MAR";
            case 4: mmm = "ABR";
            case 5: mmm = "MAY";
            case 6: mmm = "JUN";
            case 7: mmm = "JUL";
            case 8: mmm = "AGO";
            case 9: mmm = "SEP";
            case 10: mmm = "OCT";
            case 11: mmm = "NOV";
            case 12: mmm = "DIC";
                
                break;
            default:
                throw new AssertionError();
        }
        
        return dia+" "+mmm+" "+ano;//10 DIC 2011
    }
    
        public String toFyH(String fecha, String hora){//11 DIC 2011, 12:20
        String dia = fecha.substring(0, 2);
        String mmm = fecha.substring(3, 6);
        String ano = fecha.substring(7);
        
        String mes = null;
        if(mmm.equals("ENE")) mes = "1";
        if(mmm.equals("FEB")) mes = "2";
        if(mmm.equals("MAR")) mes = "3";
        if(mmm.equals("ABR")) mes = "4";
        if(mmm.equals("MAY")) mes = "5";
        if(mmm.equals("JUN")) mes = "6";
        if(mmm.equals("JUL")) mes = "7";
        if(mmm.equals("AGO")) mes = "8";
        if(mmm.equals("SEP")) mes = "9";
        if(mmm.equals("OCT")) mes = "10";
        if(mmm.equals("NOV")) mes = "11";
        if(mmm.equals("DIC")) mes = "12";

        String out = dia+"-"+mes+"-"+ano+" a las "+hora;
        return out;//20-10-2011 a las 17:15 
    }
        public String toREF(String fecha){//11 DIC 2011
        String dia = fecha.substring(0, 2);
        String mmm = fecha.substring(3, 6);
        String ano = fecha.substring(9);
        
        String mes = null;
        if(mmm.equals("ENE")) mes = "1";
        if(mmm.equals("FEB")) mes = "2";
        if(mmm.equals("MAR")) mes = "3";
        if(mmm.equals("ABR")) mes = "4";
        if(mmm.equals("MAY")) mes = "5";
        if(mmm.equals("JUN")) mes = "6";
        if(mmm.equals("JUL")) mes = "7";
        if(mmm.equals("AGO")) mes = "8";
        if(mmm.equals("SEP")) mes = "9";
        if(mmm.equals("OCT")) mes = "10";
        if(mmm.equals("NOV")) mes = "11";
        if(mmm.equals("DIC")) mes = "12";

        String out = dia+mes+ano;
        return out;//BSmmddyy 
    }
        
    
}
